ADVANCED DATABASES REPORT

Theater Organization

17 JANVIER 2021

EFREI PARIS

VARATHALINGAM Mayouran – DELPHIN Gilbert

Table of Contents

I. Introduction / M&G………………………….2

II. Short description of the Network….……..2

III. Modelization/ Function / Queries /

Triggers / Procedures………………………3

I. ERD & Relational Schema……………….3

II. Functions/ Queries /Triggers /

Procedures

1. Organization………………………..5

2. Ticketing…………………………….7

3. Accounting………………………...12

4. Network…………………………….17

IV. Encountered Problems…………………...19

V. Conclusion …………………………………20

I. Introduction / M&G

Theater is the art of writing and performing plays but it’s also the structure which have

rows seats, from where people can watch a performance. In fact, theaters are less and less

watched by people due to all other activities deemed more interesting.

So how can all theatrical companies still survive in these years of digital dominance?

To face this problem some theatrical companies had the idea of making a common

management of all their shows, so their theaters can welcome many other theatrical

companies and their staff can play in other places and not only on their theater respectively.

M&G our team is composed of VARATHLINGAM Mayouran and DELPHIN Gilbert, we

are main partners for these theaters to create all the needed databases. Our job is to help

them to manage correctly this network and optimize it to see all the advantages.

II. Short description of this network

How can these theatrical companies manage this new network?

If a theater want a company to play their show on their place, the theater has to pay

the company some costs ( comedians’ fees , staging costs and travel costs).

It’s not the case if the theater want it’s company to perform (there will be no travel costs).

Concerning the ticketing all the revenues from any representation go to the theater

where the show took place. For every representation there is an Initial Price and some

promotions can be applied depending on the situation of the client (Student , Children..) , days

separating the representation date and the ticket sell date or the load factor of the theater

for the particular representation.

Moreover, some grants are accorded to theaters depending of their cities or

supporter. For the case of Municipalities or Ministry of Culture there are negotiated with the

theater and have fixed durations.

So, we should consider all these functionalities and add some to correctly manages

this network.

III. Modelization/ Functions/ Queries / Triggers / Procedures

I. ERD & Relational Schema

II. Functions / Queries / Triggers / Procedures

We model our network to answers some questions.

1.Organization

Is no representation of a company does not overlap anothor one?

For answering this question, we created a trigger to not allow another show to happened on

the same theater at the same date.

So, this trigger count if there is any representation which has the same date that the one which

we want to enter, if it’s not the case it’ll let it insert but if it’s we will have an error message.

So, let’s try an example to see if this trigger works.

We put the second representation at the same date and theater like the first one.

We tried to run it, but this message appeared.

It shows that the trigger works, and we answered that question correctly.

What is the set of cities in which a company plays for a certain time period?

So, we had to do 2 Inner Join to access all the columns we want, and we have as

a result the cities where all companies play.

2.Ticketing

So first we will create a trigger to fill the TicketSold table, this table contains the TicketId and

the FinalPrice with the promotion added.

So, depending on the Status of the client TicketSold will contain the price the client paid.

So, there are many Status. Children, Student etc but we also have C1, C2,

C3 that are respectively those promotion.

You can see that Ticket have all information concerning the initial price the client and the

ticket buying date but Ticket Sold has the exact amount of what the client paid to the cash

desk.

We can now answer to three questions concerning Ticketing.

What is the ticket price today?

For this example, we took the date 27/12/2020 as today date.

This trigger check the date condition,

in fact if it’s 15 days before the representation the clinet can benefit 20% of reduction on

the initial price and it’s also the case if it’s the day of representation and the

room is not filled enough ( less than 50% and less than 30%)

The help of function was needed to correctly make the trigger work. Those function returned the

Theater capacity and the number of Ticket Sold to check promotion conditions on the trigger.

So, we can see that if a client wants to know the initial prices for shows in the theater, it’ll shows

us those shows with prices different from the initial price and depending on the date and the filling of

shows.

What is the distribution of tickets by price?

The second question is concerning the distribution of ticket price, in fact with discounts

applied on tickets the same tickets shows can be sold with different prices.

What is the average load factor?

So first what is the definition of average load?

We took the number of people who bought tickets for a specific show divide by the capacity

of the theater in which the show will be played.

The help of functions was needed.

We created a procedure to shows us the average load factor of each theater.

Those numbers are very low considering our database with few tickets sold but a great

capacity for theaters.

3.Accounting

For this part we created a table name TheaterBalance, a table that will allow each theater to

know their current balance. To make this table work perfectly, we created a trigger that will

update the current balance of a theater for each ticket sold.

Basically, this trigger will be activated if we insert a new ticket in the table Ticket. So, if a new

ticket is sold today (it means that we insert a new ticket) then this trigger will update the

current balance of the theater concerned. And of course, this trigger checks the client type to

apply an eventual discount.

Here is an example:

We inserted a new ticket today, for the representation 7. This representation takes place in

the 8th theater, so it means that in the table TheaterBalance the trigger will update the 8th

theater’s current balance which was in the beginning at 0.

Here we can see that the trigger updated the 8th theater’s current balance. It was at zero but

now it is at 32. It is the price of the ticket sold. But we have a problem here. In fact, when a

show is programmed for a theater, it induces that the theater has to pay a global price as

indicated in the subject. To do this we created another trigger that will update the table

TheaterBalance each time we insert a new cost. Actually, a new cost will be inserted when a

new representation is programmed.

Here is an example:

Here we inserted two new representation that are programmed for today. We can see that

one take place in the 7th theater and the other one in the 8th.

As we inserted two new representations, we will also insert their costs.

Normally, the trigger has updated the TheaterBalance table.

We can see that the current balance of the concerned theater has been updated. Now we

have a fully functional TheaterBalance table.

To be honest we did not really understand the following questions.

So, what we did is that if the current balance of a theater is less or equal to 0 then the balance

will be red. Else the balance will be green. You can refer to our trigger’s screenshots above to

see when we update the balance status.

A show given by a company was sold out with a certain price. Is it cost effective for the

company? (Compared to costs incurred (salaries, travel, staging))

To answer this question, we created a procedure that displays each companies’ final cost for

each show.

Before

After

Here we can see the final cost of each companies for each show. We can clearly see if it was

cost effective for the company or not.

Was it the effective cost for the theater? (Costs / ticketing)

For this question, we did the same as before. We created a procedure that displays the final

cost of each theater for each show.

We can see that some theaters don’t have any cost, it is because no tickets were sold yet for

this show.

4.Network

The last section is concerning the network itself.

Are there companies that will never play in their theater?

So, for answering this question we checked if some companies ever played on their theater

with the help of this function that return the number of companies that play in their theater.

Then with the procedure depending on companies we display companies that never played in

their theater

The small problem with this statement is that it can display the same company if it plays many

times outside and never in their theater.

Now let’s see the most popular shows in a certain period

First, the most popular show in number of representations played in the month of December.

Then the most popular show regarding the number of seats sold.

IV. Encountered Problems

The first problem we encountered was about the software itself. In fact, we were used to

use sql server management studio. So, we naturally began our project with this software.

But we quickly realized that the syntax for triggers was not the same as the one we saw in

class with oracle. So, we wasted a big amount of time searching how to continue the project.

We tried on the oracle VM, but we could not import our initial script from our computer.

Furthermore, oracle VM was not the ideal software to use for a project.

We finally use Oracle Live Sql to do our project even if it was not the ideal software for a

project too. Our session expired multiple times while we were coding and we had to re-run

all the statements we did previously. Moreover, we also had many errors while coding on

live sql, errors that we didn’t understand the first time. The syntax in Oracle change from

what we used to do in sql server, so errors due to syntax and others occurred frequently.

And here again we spent a lot of time to understand and correct the errors even if it was a

small one. Finally, we managed to finish the project but honestly, we think that the next

time, it will be good to have a proper software in which we practiced. So that we can do our

project according to what we saw in class without wasting time.

V. Conclusion

In conclusion, we must admit that we are proud of our project and our investment of

time to finish correctly all queries.

This project helped us to understand how to use triggers, functions and procedures

but also to improve our skills in SQL. Despite having some difficulties with the program used,

SQL Oracle was a new program for us but thanks to our TP on triggers and function we

understand easily how to use Oracle SQL Developer. Again, we would like to thank Mr. Issam

Falih for his help concerning the understanding of the project. For further details about the

code please check our commented script on triggers/functions/procedures.